﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace HuaCheng.DataAccess
{
    /// <summary>
    /// 测试方案的数据库操作
    /// </summary>
    public class TestHelper
    {
        /// <summary>
        /// 根据设备号，柜号，通道号查测试Id
        /// </summary>
        /// <param name="devId"></param>
        /// <param name="uId"></param>
        /// <param name="cId"></param>
        /// <returns></returns>
        public static string GetTestId(string ip, int gid, int mid,int cId)
        {
            string testId = "";
            string sqlStr = "select distinct test_id from test where dev_ip = @ip and cabinet_id = @gid and mid_id=@mid and chl_id = @cid";
            DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sqlStr);
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@cid", cId));
            try
            {
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            testId = Convert.ToString(reader[0]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return testId;
        }
        /// <summary>
        /// 获取最大的测试Id
        /// </summary>
        /// <returns></returns>
        //public static Test GetMaxTestId(string ip,int gid,int mid,int cid)
        //{
        //    string sqlStr = "select * from test where devIp=@devip and gui_id = @gid and mac_id=@mid and chl_id=@cid";
        //    DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sqlStr);
        //    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
        //    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
        //    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
        //    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@cid", cid));
        //    Test test=new Test();
        //    try
        //    {
        //        using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
        //        {
        //            if (reader.HasRows)
        //            {
        //                while (reader.Read())
        //                {
        //                    test = new Test()
        //                    {
        //                        TestID = Convert.ToString(reader["test_id"]),
        //                        StartTime = Convert.ToString(reader["start_time"])
        //                    };
        //                }
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex.Message + ex.StackTrace);
        //    }
        //    return test;
        //}
        /// <summary>
        /// 查询通道测试信息
        /// </summary>
        /// <param name="devip"></param>
        /// <param name="uid"></param>
        /// <param name="cid"></param>
        /// <returns></returns>
        public static Test GetTestInfo(string testId,string devip, int gid, int mid,int cid)
        {
            Test test = new Test();
            string sqlStr = "";
            if (testId != "0")
            {
                sqlStr = "select * from test where dev_ip = @ip and cabinet_id = @gid and mid_id=@mid and chl_id = @cid";
            }
            else
            {
                sqlStr = "select * from h_test where test_id=@testid and dev_ip = @ip and cabinet_id = @gid and mid_id=@mid and chl_id = @cid";
            }
            DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sqlStr);
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@testid", testId));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", devip));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@cid", cid));
            try
            {
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            test.DevIp = devip;
                            test.MID = mid;
                            test.GID = gid;
                            test.DevMid = cid;
                            test.StartStepNum = Convert.ToInt32(reader["startstep_id"]);
                            test.StartTime = Convert.ToString(reader["start_time"]);
                            test.Note = reader["note"]!=null?Convert.ToString(reader["note"]):"";
                            test.Creator = reader["creator"]!=null?Convert.ToString(reader["creator"]):"";
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return test;
        }
        /// <summary>
        /// 根据设备号，柜号，通道号删除记录
        /// </summary>
        /// <param name="devId">设备号</param>
        /// <param name="uId">柜号</param>
        /// <param name="cId">通道号</param>
        public static void DeleteTestById(string ip, int gid,int mid, int cId)
        {
            try
            {
                string sqlStr = "delete from test where dev_ip = @ip and cabinet_id = @gid and mid_id=@mid and chl_id = @cid";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sqlStr);
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@cid", cId));
                DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
        }
        /// <summary>
        /// 工步测试表中新增工步测试记录
        /// </summary>
        /// <param name="bts"></param>
        public static void InsertNewStepTest(Test test)
        {
            string sql = "insert into test(test_id,dev_ip,cabinet_id,mid_id,chl_id,startstep_id,start_time,creator,note,bat_code,bat_num,record_num,discharge_cap,discharge_time,inter_resistance,open_voltage,mode)" +
                " values (@testid,@devip,@gid,@mid,@chlid,@startid,@starttime,@creator,@note,@batcode,@batnum,@recordnum,@dischargecap,@dischargetime,@interres,@openvol,@mode)";
            try
            {
                using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql))
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@testid", test.TestID));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@devip", test.DevIp));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", test.GID));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", test.MID));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@chlid", test.DevMid));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@startid", test.StartStepNum));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@starttime", test.StartTime));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@creator", test.Creator));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@note", test.Note));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@batcode", test.BatCode));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@batnum", test.BatNum));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@recordnum", test.RecordNum));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@dischargecap", test.DischargeCap));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@dischargetime", test.DischargeTime));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@interres", test.InterResistance));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@openvol", test.OpenVoltage));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mode", test.Mode));
                    DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); }
        }
        /// <summary>
        /// 更新测试表中结束时间
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="cid"></param>
        /// <param name="dt"></param>
        public static void UpdateTestEndTime(string ip,int gid,int mid, int cid,DateTime dt)
        {
            string sql = "update test set end_time=@endtime where dev_ip=@ip and cabinet_id=@gid and mid_id=@mid and chl_id=@cid";
            try
            {
                using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql))
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@endtime", dt));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@cid", cid));
                    DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); }
        }
        /// <summary>
        /// 更新测试表中的记录条数
        /// </summary>
        /// <param name="ip"></param>
        /// <param name="uid"></param>
        /// <param name="cid"></param>
        /// <param name="recordNum"></param>
        public static void UpdateTestRecordNum(string testId,string ip, int gid,int mid, int cid, int recordNum)
        {
            string sql = "update test set record_num=@record where test_id=@testid and dev_ip=@ip and cabinet_id=@gid and mid_id=@mid and chl_id=@cid";
            try
            {
                using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql))
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@record", recordNum));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@testid", testId));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@cid", cid));
                    DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); }
        }

        /// <summary>
        /// 查询记录条数
        /// </summary>
        /// <param name="testId"></param>
        /// <param name="ip"></param>
        /// <param name="gid"></param>
        /// <param name="mid"></param>
        /// <param name="cid"></param>
        /// <returns></returns>
        public static int QueryTestRecordNum(string testId, string ip, int gid, int mid, int cid)
        {
            string sql = "select record_num from h_test where test_id=@testid and dev_ip=@ip and cabinet_id=@gid and mid_id=@mid and chl_id=@cid";
            int recordNum=0;
            try
            {
                using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql))
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@testid", testId));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@cid", cid));
                    DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd);
                    while (reader.Read())
                    {
                        recordNum = Convert.ToInt32(reader["record_num"]);
                    }
                }
            }
            catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); }
            return recordNum;
        }
        /// <summary>
        /// 根据查询条件查询历史测试表
        /// </summary>
        /// <param name="sql"></param>
        public static List<Test> RetriveTestInfo(string sql)
        {
            DbCommand dbcmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
            List<Test> testInfo = new List<Test>();
            try
            {
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(dbcmd))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {

                            Test test = new Test()
                            {
                                TestID = Convert.ToString(reader["test_id"]),
                                DevIp = reader["dev_ip"].ToString(),
                                GID = Convert.ToInt32(reader["cabinet_id"]),
                                MID = Convert.ToInt32(reader["mid_id"]),
                                DevMid = Convert.ToInt32(reader["chl_id"]),
                                StartTime = Convert.ToString(reader["start_time"]),
                                EndTime = Convert.ToString(reader["end_time"]),
                                RecordNum = Convert.ToInt32(reader["record_num"]),
                                Creator = Convert.ToString(reader["creator"]),
                                Note = Convert.ToString(reader["note"]),
                                BatNum = Convert.ToString(reader["bat_num"]),
                                BatCode = Convert.ToString(reader["bat_code"]),
                                DischargeCap = Convert.ToSingle(reader["discharge_cap"]),
                                DischargeTime = Convert.ToString(reader["discharge_time"]),
                                InterResistance = Convert.ToSingle(reader["inter_resistance"]),
                                OpenVoltage = Convert.ToSingle(reader["open_voltage"]),
                                Mode = Convert.ToInt32(reader["mode"])
                            };
                            testInfo.Add(test);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return testInfo;
        }
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        public static void ExcuteSQL(string sql)
        {
            DbCommand dbcmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
            try
            {
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(dbcmd))
                {
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message + e.StackTrace);
            }
        }
    }
}
